package com.servlet;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

/**
 * Servlet implementation class DatabaseConnServlet
 */
public class SearchFlightServlet extends HttpServlet {
	private static final long serialVersionUID = 1L;
	
private final static String DATASOURCE_NAME = "jdbc/FlyGreenwichAirline";
	
	private DataSource datasource;
  
    /**
     * @see HttpServlet#HttpServlet()
     */
    public SearchFlightServlet() {
        super();
        // TODO Auto-generated constructor stub
    }

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request,response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		
		String depart = "";
		String dest = "";
		String cost = "";
		Integer id = 0;
		String time_available = "";
		List timelist = new ArrayList();  
		
		java.sql.Connection con=null;
		
		String departure = request.getParameter("departure");
		String destination = request.getParameter("destination");
		
		System.out.println("Departure chosen: " + departure);
		System.out.println("Destination chosen: " + destination);

    	String query = "SELECT * FROM flight WHERE dest = '"+destination+"' AND depart = '"+departure+"';";
    	
						
    	try {
    		Context initialContext = new InitialContext();
    		Context context = (Context)initialContext.lookup("java:comp/env");
    		datasource = (DataSource)context.lookup(DATASOURCE_NAME);
    		
    		con = datasource.getConnection();


            java.sql.Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery (query);
            
            while(rs.next()){
            id = rs.getInt(1);
            depart = rs.getString(2);
            dest = rs.getString(3);
            cost = rs.getString(4);
            
            System.out.println("id: "+id);
            System.out.println("depart: "+depart);
            System.out.println("dest: "+dest);
            System.out.println("cost: "+cost);

            }
            
            String query2 = "select datetime from schedule where flight_id = "+id+";";
            
            java.sql.Statement stmt2 = con.createStatement();
            ResultSet rs2 = stmt2.executeQuery (query2);
            
            while(rs2.next()){
            	
            	Timestamp datetime = rs2.getTimestamp(1);
            	time_available = new SimpleDateFormat("MM/dd/yyyy").format(datetime);
            	System.out.println("time_available: "+time_available);
            	
            	timelist.add(time_available);
            	
            }
            	
            rs.close();
            stmt.close();
            rs2.close();
            stmt2.close();
            
        }  // end try

    catch (SQLException ex) {
            
		while (ex != null) {  
				ex.printStackTrace();
              }  // end while

        }  // end catch SQLException

   catch (java.lang.Exception ex) {
      	ex.printStackTrace();
	  }
    	
    	finally{
    		try{
    			if (con != null) con.close();
    		}catch(SQLException ignored){
    		ignored.printStackTrace();
    		}
    	}
    	
    	request.setAttribute("flight_id", id);
        request.setAttribute("depart", depart);
        request.setAttribute("dest", dest);
        request.setAttribute("cost", cost);
        request.setAttribute("time", timelist);

    	String nextJSP = "/searchResults.jsp";
    	RequestDispatcher dispatcher = getServletContext().getRequestDispatcher(nextJSP);
    	dispatcher.forward(request,response);
	}
	
}
